package io.aicn.lowcode.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

@Service
public class SqlExecutionService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Transactional
    public List<Map<String, Object>> executeQuery(String sql) {
        return jdbcTemplate.queryForList(sql);
    }
    
    @Transactional
    public int executeUpdate(String sql) {
        return jdbcTemplate.update(sql);
    }
    
    @Transactional
    public int[] executeBatch(List<String> sqlStatements) {
        return jdbcTemplate.batchUpdate(sqlStatements.toArray(new String[0]));
    }
    
    @Transactional
    public void executeSql(String sql) {
        if (sql.trim().toUpperCase().startsWith("SELECT")) {
            executeQuery(sql);
        } else {
            executeUpdate(sql);
        }
    }
    
    @Transactional
    public void executeSqlList(List<String> sqlStatements) {
        System.out.println("Executing " + sqlStatements.size() + " SQL statements:");
        for (int i = 0; i < sqlStatements.size(); i++) {
            String sql = sqlStatements.get(i);
            System.out.println("SQL[" + i + "]: " + sql);
            try {
                executeSql(sql);
                System.out.println("SQL[" + i + "] executed successfully");
            } catch (Exception e) {
                System.out.println("SQL[" + i + "] failed: " + e.getMessage());
                throw e;
            }
        }
    }
    
    public List<String> getTableNames() {
        return jdbcTemplate.queryForList("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'lowcode'", String.class);
    }
    
    public List<Map<String, Object>> getTableSchema(String tableName) {
        return jdbcTemplate.queryForList(
            "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT " +
            "FROM INFORMATION_SCHEMA.COLUMNS " +
            "WHERE TABLE_SCHEMA = 'lowcode' AND TABLE_NAME = ? " +
            "ORDER BY ORDINAL_POSITION", 
            tableName
        );
    }
}